//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using CommunityServer.Components;
using CommunityServer.Galleries.Components;
using System.Collections;
using System.Text;
using Telligent.CommunityServer.MetadataExtractor.Exif;

namespace CommunityServer.SqlDataProvider
{
	/// <summary>
	/// Impliments Application Sepecific components of the BaseThreadQueryBuilder.BuildQuery method
	/// </summary>
	public class GalleryThreadQueryBuilder : BaseThreadQueryBuilder
	{
		private GalleryThreadQuery query;
		public GalleryThreadQueryBuilder(ThreadQuery query, string databaseOwner): base(query, databaseOwner)
		{
			this.query = (GalleryThreadQuery)query;
		}

		#region QueryBulder Overrides

		/// <summary>
		/// FROM clause element to add additional tables to the query 
		/// Adds the EXIF Picture DateTime if available for additional sorting
		/// </summary>
		protected override void AddAditionalTables()
		{
			sb.AppendFormat("left join {0}.cs_PostMetadata M on (M.PostID = P.PostID and M.MetaKey = '{1}:{2}') ", databaseOwner, typeof(ExifDirectory).FullName, ExifDirectory.TAG_DATETIME_ORIGINAL);
		}

		/// <summary>
		/// ORDER BY, HAVING and GROUP BY objects appended after the WHERE clause
		/// </summary>
		protected override void ApplySort()
		{
			string order = "desc";
			if(query.SortOrder == SortOrder.Ascending)
				order = "asc";

			// Sort by
			string groupBy = string.Empty;
			string orderBy = string.Empty;
			switch(query.SortBy)
			{
				case GalleryThreadSortBy.Author:
					orderBy = " order by P.PostAuthor";
					groupBy = "P.PostAuthor";
					break;
				case GalleryThreadSortBy.Comments:
					orderBy = " order by T.TotalReplies";
					groupBy = "T.TotalReplies";
					break;
				case GalleryThreadSortBy.Rating:
					orderBy = " order by case when T.TotalRatings > 0 then (convert(decimal,T.RatingSum) / convert(decimal,T.TotalRatings)) else 0 end";
					groupBy = "T.TotalRatings, T.RatingSum";
					break;
				case GalleryThreadSortBy.Subject:
					orderBy = " order by P.Subject";
					groupBy = "P.Subject";
					break;
				case GalleryThreadSortBy.Views:
					orderBy = " order by T.TotalViews";
					groupBy = "T.TotalViews";
					break;
				default:
				case GalleryThreadSortBy.ThreadDate:
					orderBy = " order by P.PostDate";
					groupBy = "P.PostDate";
					break;
				case GalleryThreadSortBy.PictureDate:
					orderBy = " order by M.MetaValue " + order + ", P.PostDate";
					groupBy = "M.MetaValue, P.PostDate";
					break;
			}

			// Add group by and having if necessary, then order by
			if(query.UncategorizedOnly)
			{
				sb.Append(" group by P.PostID, " + groupBy);
				sb.Append(" having count(PC.CategoryID) = 0");
			}
			sb.Append(orderBy + " " + order);
		}

		/// <summary>
		/// WHERE clause element to specify the valid IsApproved / PostDate values
		/// </summary>
		protected override void ApplyPublished()
		{
			//Do we care if the post has been publshied
			if(query.PublishedFilter != GalleryPostPublishedFilter.All)
			{
				sb.AppendFormat(" and P.IsApproved = {0} and P.PostDate <= getdate() ", (int)query.PublishedFilter);
			}
		}

		/// <summary>
		/// WHERE clause element to speficy the ApplicationPostType to return (bitwise comparison)
		/// </summary>
		protected override void ApplyPostType()
		{
			sb.AppendFormat(" and P.ApplicationPostType & {0} <> 0 ", (int)query.ApplicationPostType);
			//sb.AppendFormat(" and P.ApplicationPostType = {0} ", (int)query.PostType);
		}

		/// <summary>
		/// WHERE clause elemenet to apply values set in the FilterKey
		/// The base only runs this if the key length is greater than zero
		/// </summary>
		protected override void ApplyFilterKey()
		{
			sb.AppendFormat(" and S.SectionID in ({0}) ", query.FilterKey);
		}

		protected override string GetSectionIDList()
		{
			ArrayList galleries = Galleries.Galleries.GetGalleries();
			if (galleries.Count > 0)
			{
				StringBuilder list = new StringBuilder();
				foreach (Gallery g in galleries)
				{
					if (list.Length > 0)
						list.Append(",");

					list.Append(g.SectionID);
				}

				return list.ToString();
			}
			else
				return "null";
		}

		#endregion
	}
}
